第一题 学生成绩

【数组公式】3. 使用数组公式，根据Sheet1中的数据，计算总分和平均分，将其结果保存到表中的“总分”列和“平均分”列。

总分列选中整一列后，在公式编辑栏输入=C2:C39+D2:D39+E2:E39，然后同时摁Ctrl+Shift+Enter。

平均分列同样操作，选中整一列后在公式编辑栏输入=(C2:C39+D2:D39+E2:E39)/3，然后同时摁Ctrl+Shift+Enter。

【排序函数】4. 使用RANK函数，根据Sheet1中的“总分”列对每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列。（如果多个数值排名相同，则返回该组数值的最佳排名）

F2单元格中输入=RANK(F2,$F$2:$F$39)，然后进行自动填充，注意排序区域的绝对引用。

最佳排名使用的函数本应是RANK.EQ，但使用RANK函数也可得到相应分数。

RANK(number,ref,[order])

RANK.EQ(number,ref,[order])

【逻辑函数】5. 使用逻辑函数，判断Sheet1中每个同学的每门功课是否均高于全班单科平均分。

I2单元格中输入=IF(AND(C2>=AVERAGE($C$2:$C$39),D2>AVERAGE($D$2:$D$39),E2>=AVERAGE($E$2:$E$39)),TRUE,FALSE)，然后进行自动填充，注意排序区域的绝对引用。

IF(Logical_test,Value_if_true,Value_if_false)

【统计函数】6. 根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。

在Sheet2中选中B2单元格，输入=COUNTIF(Sheet1!D2:D39,"110")-COUNTIF($G$3:$G$52,">850")，注意绝对引用。

【求和函数】5. 使用函数，计算每个用户所订购图书所需支付的金额，并将结果保存在Sheet1中“用户支付情况表”的“支付总额”列中。

在L8单元格输入=SUMIF($A$3:$A$52,K8,$I$3:$I$52)，然后向下进行自动填充，注意求和区域的绝对引用。

【闰年公式】6. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”；如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet2表B2单元格中输入=IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)0)),"闰年","平年")，然后向下进行自动填充。

第四题 号码升级

【闰年公式】2. 在Sheet5的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。

闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet5的B1单元格中输入=IF(OR(MOD(YEAR(NOW()),400)=0,AND(MOD(YEAR(NOW()),4)=0,MOD(YEAR(NOW()),100)0)),TRUE,FALSE)。

MOD(Number,Divisor)

YEAR(Serial_Number)

【时间函数】3. 使用时间函数，对Sheet1中用户的年龄进行计算。

在D2单元格中输入=YEAR("2013-5-1")-YEAR(C2)，然后向下进行自动填充。

【号码升级】4. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。

在G2单元格中输入=REPLACE(F2,5,0,"8")，然后向下进行自动填充。

REPLACE(Old_text,Start_num,Num_chars,New_text)

【逻辑函数】5. 在Sheet1中，使用AND函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于40岁的男性，并将结果保存在“是否>=40男性”列中。

在H2单元格中输入=IF(AND(D2>=40,B2="男"),TRUE,FALSE)。

【统计函数】6. 根据Sheet1中的数据，对以下条件，使用统计函数进行统计。

在Sheet2表B2单元格中输入=COUNTIF(Sheet1!B2:B37,"男")。

在Sheet2表B3单元格中输入=COUNTIF(Sheet1!D2:D37,">40")。

第五题 采购情况表

【数据查重】2. 在Sheet1中设定第31行不能输入重复的数值。

“允许”选择自定义，勾选忽略空值，公式输入=COUNTIF(31:31,A31)=1。

【数组公式】3. 使用数组公式，计算Sheet1中的每种产品的价值，将结果保存到表中的“价值”列中。

选中“价值”列，在公式编辑栏中输入=E2:E17*F2:F17*G2:G17。

【数据库函数】4. 在Sheet2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存在相应的单元格中。

在Sheet2表G23单元格中输入=DAVERAGE(A2:H18,E2,J3:L4)。

DAVERAGE(Database,Field,Critaria)

在Sheet2表G24单元格中输入=DSUM(A2:H18,G2,J8:L9)。

DSUM(Database,Field,Critaria)

【计数函数】5. 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。根据调查情况，制作出Sheet3。请使用函数，统计符合以下条件的数值。

在Sheet3表B14单元格中输入=COUNTBLANK(B2:E11)。

COUNTBLANK(Range)。

在Sheet3表B15单元格中输入=COUNTIF(B2:E11,"Y")。

【文本函数】6. 使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否为文本，如果是，结果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格当中。

在Sheet3表B22单元格中输入=ISTEXT(B21)。

ISTEXT(Value)。